package pers.zxk.jlytnews.dao.impl;

import pers.zxk.jlytnews.dao.NewsDao;
import pers.zxk.jlytnews.entity.Food;
import pers.zxk.jlytnews.entity.News;
import pers.zxk.jlytnews.util.DBconn;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class NewsDaoImpl implements NewsDao {
    @Override
    public boolean addNews(int type, String title, String icon, String url, String contents,
                           String date, String source) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        int i = DBconn.addUpdDel("insert into tb_news(type,title,icon,url,contents,date,source) " +
                "values('" + type
                + "','" + title
                + "','" + icon
                + "','" + url
                + "','" + contents
                + "','" + date
                + "','" + source
                + "')");
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean delNews(int id) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        String sql = "delete from tb_news where id=" + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public boolean updateNews(int id, int type, String title, String icon, String url, String contents,
                              String date, String source) throws SQLException, ClassNotFoundException {
        boolean flag = false;
        DBconn.init();
        String sql = "update tb_news set type ='" + type
                + "' , title ='" + title
                + "' , icon ='" + icon
                + "' , url ='" + url
                + "' , contents ='" + contents
                + "' , date ='" + date
                + "' , source ='" + source
                + "' where id = " + id;
        int i = DBconn.addUpdDel(sql);
        if (i > 0) {
            flag = true;
        }
        DBconn.closeConn();
        return flag;
    }

    @Override
    public News getNews(int id) throws SQLException, ClassNotFoundException {
        DBconn.init();
        ResultSet rs = DBconn.selectSql("select * from tb_news where id = " + id);
        if (rs.next()) {
            News news = new News(id,
                    rs.getInt("type"),
                    rs.getString("title"),
                    rs.getString("icon"),
                    rs.getString("url"),
                    rs.getString("contents"),
                    rs.getString("date"),
                    rs.getString("source"));
            DBconn.closeConn();
            return news;
        } else {
            DBconn.closeConn();
            throw new SQLException("未找到id为" + id + "的数据");
            //return null;
        }
    }

    @Override
    public List<News> getLatestNews(int count) throws SQLException, ClassNotFoundException {
        List<News> list = new ArrayList<News>();
        DBconn.init();
        ResultSet rs;
        if (count > 0) {
            rs = DBconn.selectSql("select * from tb_news order by date DESC limit " + count);
        } else if (count == 0) {
            rs = DBconn.selectSql("select * from tb_news order by date DESC");
        } else {
            throw new SQLException("查询数量不能为负数");
        }
        while (rs.next()) {
            News news = new News();
            news.setId(rs.getInt("id"));
            news.setType(rs.getInt("type"));
            news.setTitle(rs.getString("title"));
            news.setIcon(rs.getString("icon"));
            news.setUrl(rs.getString("url"));
            news.setContents(rs.getString("contents"));
            news.setDate(rs.getString("date"));
            news.setSource(rs.getString("source"));
            list.add(news);
        }
        DBconn.closeConn();
        return list;
    }

    /**
     * 分页查询 --（未考虑增删记录导致的数据重复或遗漏情况）
     * 查询第page页开始的size条记录数
     *
     * @param page 页码
     * @param size 每页记录数
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<News> getNewsList(int page, int size) throws SQLException, ClassNotFoundException {
        List<News> list = new ArrayList<News>();
        DBconn.init();
        ResultSet rs;

        if (size < 0) {
            throw new SQLException("查询数量不能为负数");
        }

        if (page < 0) {//页码小于0时，查询第0页
            page = 0;
        }

        String sql = null;
        if (page == 0 && size == 0) { // page,size  都为0时 查询全部数据
            sql = "select * from tb_news order by date DESC,id DESC";
        } else {
            //正常查询：扫描记录数为page*size+size，效率较低，高并发时应优化
            sql = "select * from tb_news order by date DESC,id DESC limit " + page * size + "," + size;
            //使用子查询嵌套优化：子查询只返回id速度很快，外层查询扫描记录数为size
            //String sql1 = "select * from tb_news where id>=(select id from tb_news limit " + page * size + ",1) order by date,id DESC limit 0," + size;
        }
        rs = DBconn.selectSql(sql);

        while (rs.next()) {
            News news = new News();
            news.setId(rs.getInt("id"));
            news.setType(rs.getInt("type"));
            news.setTitle(rs.getString("title"));
            news.setIcon(rs.getString("icon"));
            news.setUrl(rs.getString("url"));
            news.setContents(rs.getString("contents"));
            news.setDate(rs.getString("date"));
            news.setSource(rs.getString("source"));
            list.add(news);
        }
        DBconn.closeConn();
        return list;
    }

    /**
     * 分页查询 --（考虑增删记录导致的数据重复或遗漏情况）
     * 查询id大于lastId的size条记录数
     *
     * @param lastId id
     * @param size   每页记录数
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    @Override
    public List<News> getNewsListPlus(int lastId, int size) throws SQLException, ClassNotFoundException {
        List<News> list = new ArrayList<News>();
        DBconn.init();

        if (size <= 0) {
            throw new SQLException("查询数量不能为负数");
        }

        if (lastId <= 0) {//id小于0时，查询最新的size条记录
            return getNewsList(0, size);
        } else {
            String sql = "select * from tb_news where id>" + lastId + " order by date,id DESC limit 0," + size;
            ResultSet rs = DBconn.selectSql(sql);

            while (rs.next()) {
                News news = new News();
                news.setId(rs.getInt("id"));
                news.setType(rs.getInt("type"));
                news.setTitle(rs.getString("title"));
                news.setIcon(rs.getString("icon"));
                news.setUrl(rs.getString("url"));
                news.setContents(rs.getString("contents"));
                news.setDate(rs.getString("date"));
                news.setSource(rs.getString("source"));
                list.add(news);
            }
            DBconn.closeConn();
            return list;
        }
    }
}
